class Syn
  def self.cron_syn
    print "Syn to sqlite to postgres\n"
    print "vacuum...\n"
    Weekly.connection.execute "vacuum;"
    print "weekly\n"
    Pg::Connect.transaction do
      Weekly.all(:conditions => ["id > ? and wid is not null", Pg::Weekly.maximum('id') || 0]).each do |weekly|
	    Pg::Connect.connection.execute "insert into #{Pg::Weekly.table_name} (id) values ('#{weekly.id}');"
	    Pg::Weekly.find(weekly.id).update_attributes! weekly.attributes
      end
    end
    print "weekly_result_work\n"
    max_weekly_result_work_id = Pg::WeeklyResultWork.maximum('id') || 0
    Pg::Connect.transaction do
      WeeklyResultWork.all(:conditions => ["id > ?", max_weekly_result_work_id]).each do |work|
	    Pg::Connect.connection.execute "insert into #{Pg::WeeklyResultWork.table_name} (id) values ('#{work.id}');"
	    Pg::WeeklyResultWork.find(work.id).update_attributes! work.attributes
      end
    end
    print "weekly_result\n"
   max_weekly_id = Pg::Weekly.maximum('id') || 0
   result_count = 0
    while true do
	    p result_count  if (result_count % 37).zero?
      results = WeeklyResult.all(:conditions => ["id > ? and weekly_id <= ?", Pg::WeeklyResult.maximum('id') || 0, max_weekly_id],
         :limit => 2000, :order => "id")
      break if results.empty?
      doc = File.new("#{Rails.root}/_result.csv", 'w')
      str = "copy weekly_results (id, weekly_id, clicks, clicks_rank, comments, comments_rank, danmu, danmu_rank, part, score, score_rank, stows, stows_rank, sp_type_id, tj, tj_rank, yb, yb_rank, weekly_result_work_id)
from stdin with csv;\n"
      str += results.inject("") do |sr, r|
        sr << %[#{r.id},#{r.weekly_id},#{r.clicks},#{r.clicks_rank},#{r.comments},#{r.comments_rank},#{r.danmu},#{r.danmu_rank},#{r.part},]
        sr << %[#{r.score},#{r.score_rank},#{r.stows},#{r.stows_rank},#{r.sp_type_id},#{r.tj},#{r.tj_rank},#{r.yb},#{r.yb_rank},#{r.weekly_result_work_id}]
        sr << "\n"
      end
      doc.write str
      doc.close
      system "psql alicevvc_weekly -U alicevvc -f #{Rails.root}/_result.csv"
      result_count += results.size
    end
    print "weeklyexcept\n"
    Pg::Connect.transaction do
      WeeklyExcept.all(:conditions => ["id > ? and weekly_id <= ?", Pg::WeeklyExcept.maximum('id') || 0, max_weekly_id]).each do |weekly_except|
        Pg::Connect.connection.execute "insert into #{Pg::WeeklyExcept.table_name} (id) values ('#{weekly_except.id}');"
        Pg::WeeklyExcept.find(weekly_except.id).update_attributes! weekly_except.attributes
      end
    end
    print "sp\n"
    Pg::Connect.transaction do
      SpType.all(:conditions => ["id > ?", Pg::SpType.maximum('id') || 0]).each do |sp|
	    Pg::Connect.connection.execute "insert into #{Pg::SpType.table_name} (id) values ('#{sp.id}');"
	    Pg::SpType.find(sp.id).update_attributes! sp.attributes
      end
    end
    print "vacuum...\n"
    Pg::Connect.connection.execute "vacuum full;"
    system "cd ~/ranking/ && rake tmp:cache:clear"
  end
end
